import os
import sys
from pysqlite2 import dbapi2 as sqlite
import pysqlite2
import traceback
import l_temp_ver
import re

class l_soal(object):
    def __init__(self, parent=None):
        super(l_soal, self).__init__()
        
        #self.idx = Idx
        #self.Q = Q
        #self.A = A
        
        self.header_soal = " "
        self.footer_soal = " "
        self.content_soal = " "
        
        self.conn = sqlite.connect(os.environ['TEMP'] + '\\' + 'temp.db')
        self.curs = self.conn.cursor()
        
        self.sql0 = '''DROP TABLE IF EXISTS soal'''
        self.sqlA = '''CREATE TABLE IF NOT EXISTS [soal](
                       [id] BIGINT(100) NOT NULL PRIMARY KEY,
                       [soal] VARCHAR(255) NOT NULL,
                       [jawaban] VARCHAR(255) NOT NULL,
                       [pilihan] VARCHAR(255) NOT NULL)'''
        self.sqlB = '''CREATE TABLE IF NOT EXISTS [navsoal](
                       [max_soal] BIGINT(100) NOT NULL,
                       [min_soal] BIGINT(100) NOT NULL)'''
        self.sqlC = '''CREATE TABLE IF NOT EXISTS [navnosoal](
                       [max_nosoal] BIGINT(100) NOT NULL,
                       [min_nosoal] BIGINT(100) NOT NULL)'''       
        self.sqlD = '''CREATE TABLE IF NOT EXISTS [soal](
                       [id] BIGINT(100) NOT NULL PRIMARY KEY,
                       [essay] VARCHAR(255) NOT NULL,
                       [jawaban_essay] VARCHAR(255) NOT NULL)'''
        
    def getmax_id(self):
        try:
            sql = "SELECT count(id) FROM soal"
            self.curs.execute(sql)
            self.conn.commit()
        except:
            self.curs.execute(self.sqlA)
            self.conn.commit()
            sql = "SELECT count(id) FROM soal"
            self.curs.execute(sql)
            self.conn.commit()
        
        data = self.curs.fetchall()
        data2 = data[0][0]
        #data2 = data
        #print "data2 = ", data2
        return data2
    
    def get_id(self,tsoal):
        if tsoal == 'pg' or tsoal == 'PG' or tsoal == 'Pg':
            try:
                sql = "SELECT id FROM soal"
                self.curs.execute(sql)
                self.conn.commit()
            except:
                self.curs.execute(self.sqlA)
                self.conn.commit()
                sql = "SELECT id FROM soal"
                self.curs.execute(sql)
                self.conn.commit()
            
            data = self.curs.fetchall()
            data2 = []
            for i in data:
                data2.append(str(i[0]))
            #data2 = data
            #print "data all id = ", data2
            return data2
        elif tsoal == 'essay' or tsoal == 'ESSAY' or tsoal == 'Essay':
            try:
                sql = "SELECT id FROM soal_essay"
                self.curs.execute(sql)
                self.conn.commit()
            except:
                self.curs.execute(self.sqlA)
                self.conn.commit()
                sql = "SELECT id FROM soal_essay"
                self.curs.execute(sql)
                self.conn.commit()
            
            data = self.curs.fetchall()
            data2 = []
            for i in data:
                data2.append(str(i[0]))
            #data2 = data
            #print "data all id = ", data2
            return data2
    
    def setsoalmaxmin(self, maxsoal, minsoal):
        try:
            SQL = "INSERT INTO navsoal VALUES(" + str(maxsoal) + " , " + str(minsoal) + ")"
            print "l_soal - setsoalmaxmin --> SQL = ", SQL
            self.curs.execute(self.sqlB)
            self.conn.commit()
            self.curs.execute(SQL)
            self.conn.commit()
            return (True, " ")
        except:
            datae = traceback.format_exc()
            return (False, str(datae))
        
    def setnosoalmaxmin(self, maxsoal, minsoal):
        try:
            self.curs.execute(self.sqlC)
            self.conn.commit()
            SQL = "INSERT INTO navnosoal VALUES(" + str(maxsoal) + " , " + str(minsoal) + ")"
            print "l_soal - setnosoalmaxmin --> SQL = ", SQL
            self.curs.execute(SQL)
            self.conn.commit()
            return (True, " ")
        except:
            datae = traceback.format_exc()
            return (False, str(datae))
        
    def updatesoalmaxmin(self, maxsoal, minsoal, condition):
        try:
            SQL = "UPDATE navsoal set max_soal = " + str(maxsoal) + " , min_soal = " + str(minsoal) + " WHERE max_soal = " + str(condition)
            print "l_soal - updatesoalmaxmin --> SQL = ", SQL
            self.curs.execute(self.sqlB)
            self.conn.commit()
            self.curs.execute(SQL)
            self.conn.commit()
            return (True, " ")
        except:
            datae = traceback.format_exc()
            return (False, str(datae))
        
    def updatenosoalmaxmin(self, maxsoal, minsoal, condition):
        try:
            SQL = "UPDATE navnosoal set max_soal = " + str(maxsoal) + " , min_soal = " + str(minsoal) + " WHERE max_soal = " + str(condition)
            print "l_soal - updatenosoalmaxmin --> SQL = ", SQL
            self.curs.execute(self.sqlB)
            self.conn.commit()
            self.curs.execute(SQL)
            self.conn.commit()
            return (True, " ")
        except:
            datae = traceback.format_exc()
            return (False, str(datae))
        
    def getsoalmaxmin(self):
        try:
            SQL = "SELECT * FROM navsoal"
            self.curs.execute(SQL)
            self.conn.commit()
            result = self.curs.fetchall()
            return (True, result)
        except:
            a,b,c = sys.exc_info()
            data_e = re.split("class|<|>|'", str(a))
            if data_e[-3] == "pysqlite2._sqlite.OperationalError":
                self.curs.execute(self.sqlB)
                self.conn.commit()
                try:
                    SQL = "SELECT * FROM navsoal"
                    self.curs.execute(SQL)
                    self.conn.commit()
                    result = self.curs.fetchall()
                    return (True, result)
                except:
                    datae = traceback.format_exc()
                    return(False,str(datae))
            else:
                datae = traceback.format_exc()
                return(False,str(datae))
            
    def getnosoalmaxmin(self):
        try:
            SQL = "SELECT * FROM navnosoal"
            self.curs.execute(SQL)
            self.conn.commit()
            result = self.curs.fetchall()
            return (True, result)
        except:
            a,b,c = sys.exc_info()
            data_e = re.split("class|<|>|'", str(a))
            if data_e[-3] == "pysqlite2._sqlite.OperationalError":
                self.curs.execute(self.sqlC)
                self.conn.commit()
                try:
                    SQL = "SELECT * FROM navsoal"
                    self.curs.execute(SQL)
                    self.conn.commit()
                    result = self.curs.fetchall()
                    return (True, result)
                except:
                    datae = traceback.format_exc()
                    return(False,str(datae))
            else:
                datae = traceback.format_exc()
                return(False,str(datae))
        
    def addsoal(self,Q=None, A=None, option=None):
        maxid = self.getmax_id()
        if maxid == 0 or maxid == None:
            maxid = 1
        else:
            maxid = maxid + 1
            #print "MAXID 1 = ", maxid
        try:
            sql = "INSERT INTO soal values(" + str(maxid) + ",'" + str(Q) + "','" + str(A) + "',\"" + str(option) + "\")"
            print "l_soal - addsoal -->SQL = ", sql
            self.curs.execute(sql)
            self.conn.commit()
            return (True," ")
        except:
            #info = str(str(traceback.format_exc().splitlines()[-1]).split(":")[0]).strip()
            #print "INFO = ", info
            #if "OperationalError" in str(info):
            # self.curs.execute(self.sqlA)
            # self.conn.commit()
            # self.curs.execute(sql)
            # self.conn.commit()
            # return (True," ")
            #else:
            datae = traceback.format_exc()
            return (False, str(datae)) 
        
    def addsoal2(self,essay=None,jwb_essay=None):
        maxid = self.getmax_id()
        if maxid == 0 or maxid == None:
            maxid = 1
        else:
            maxid = maxid + 1
        #print "MAXID 1 = ", maxid
        try:
            sql = "INSERT INTO soal_essay values(" + str(maxid) + ",'"  + str(essay)  + "','" + str(jwb_essay) + "')"
            print "l_soal - addsoal2 -->SQL = ", sql
            self.curs.execute(sql)
            self.conn.commit()
            return (True," ")
        except:
            #info = str(str(traceback.format_exc().splitlines()[-1]).split(":")[0]).strip()
            #print "INFO = ", info
            #if "OperationalError" in str(info):
            #    self.curs.execute(self.sqlA)
            #    self.conn.commit()
            #    self.curs.execute(sql)
            #    self.conn.commit()
            #    return (True," ")
            #else:
            datae = traceback.format_exc()
            return (False, str(datae))
        
    def getby_id(self, idx):
        #self.curs.execute(self.sqlA)
        try:
            self.conn.commit()
            sql = "SELECT * FROM soal WHERE id = " + str(idx)
            self.curs.execute(sql)
            self.conn.commit()
	    
            data = self.curs.fetchall()
            return data
        except:
            data_e = "Data tidak di temukan ? \n"
            return (False, str(data_e))
        
    def update(self,data,condition):
        try:
            data1 = str(",").join(data)
            SQL = "UPDATE soal set " + data1 + " " + str(condition)
            #SQL = "UPDATE soal set soal = '" + str(soal) + "', jawaban = '" + str(jawaban) + "', pilihan = '" + str(pilihan) + "' WHERE id = " + idx
            print "l_soal - update -> SQL UPDATE = ", SQL
            self.curs.execute(SQL)
            self.conn.commit()
            return (True, ' ')
        except:
            datae = traceback.format_exc()
            print "Error (update) = " + str(datae)
            return (False, str(datae))
        
    def getall(self):
        try:
            self.conn.commit()
            sql = "SELECT * FROM soal"
            self.curs.execute(sql)
            self.conn.commit()
	    
            data = self.curs.fetchall()
            return data
        except:
            data_e = "Data tidak di temukan ? \n"
            return (False, str(data_e))
    
    def export_soal(self,soal_type):
        import l_convert
        myclass = l_convert.convert()
        if soal_type == 'xls':
            pass
        
    def delete_navsoal(self):
        try:
            SQL = "delete from navsoal"
            self.curs.execute(SQL)
            self.conn.commit()
            print "l_soal --> delete_navsoal"
        except pysqlite2._sqlite.OperationalError:
            pass
        
    def delete_navnosoal(self):
        try:
            SQL = "delete from navnosoal"
            self.curs.execute(SQL)
            self.conn.commit()
            print "l_soal --> delete_navnosoal"
        except pysqlite2._sqlite.OperationalError:
            pass

    def split_data(self, table, idx = None):
        try:
            import l_temp_ver
            myclass = l_temp_ver.temp_ver()
            if idx == None:
                data = myclass.getAll(str(table))
            else:
                data = myclass.getAll_byid(str(table), idx)
            #print "data split data = ", data
            #print "len(data) = ", len(data[0])
            if len(data[0]) > 1:
                pre_data_A = []
                pre_data_B = []
                pre_data_C = []
                for soal in data:
                    pre_data_01 = str(soal[-1]).split("(")
                    pre_data_02 = str(pre_data_01[1]).split(")")
                    pre_data_03 = str(pre_data_02[0]).split(",")
                    pre_data_A.append(soal[0])
                    pre_data_A.append(soal[1])
                    pre_data_A.append(soal[2])
                    for j in pre_data_03:
                        x = str(j).strip()
                        y = str(x).split(x[0])
                        pre_data_C.append(y[1])
                    pre_data_A.append(pre_data_C)
                    pre_data_B.append(pre_data_A)
                    pre_data_A = []
                    pre_data_C = []
                    #pre_data.append("|")
                print "l_soal - split data --> data_pre = ", pre_data_B
                return pre_data_B
            else:
                print "\n"
                e_data = "Field not containt data ! \n"
                print e_data
        except:
            data_e = traceback.format_exc()
            print "\n"
            print " ERROR = ", data_e
